import xlrd
from sqlalchemy import MetaData, Column, Table, ForeignKey
from sqlalchemy import Integer, String
from sqlalchemy.sql import select
from sqlalchemy.sql import and_
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from class_table_sql import CLS_Table
# SQLAlchemy connexion
engine = create_engine('sqlite:///Inscritos_2010-2011-bdd.db', echo = False)
Session = sessionmaker(bind=engine)
session = Session()
# List Storage Values from Line
listbdd = []
# BDD 1 COL
ESTAB_ENSINO = ''
# BDD 2 COL
UNI_ORGANICA = ''
# BDD 3 COL
NIVEL_FORMACAO = ''
# BDD 4 COL
CURSO_SUP = ''
# BDD 5 COL
AREA_EDU_FOR = ''
# XLS Sheet Name
NAME_TABLE_XLS = "Tabela 4.1"
# XLS File Name
NAME_XLS = "Inscritos_2010-2011.xls"
# Open XLS File
workbook = xlrd.open_workbook(NAME_XLS)
# Open XLS Sheet
sh = workbook.sheet_by_name(NAME_TABLE_XLS)
# Bol Check Empty COL Title XLS on Sheet
checkCol = 0
# If COL Title empty
for col in range(sh.ncols):
    if (sh.cell(3,col).value == ""):
        checkCol = col
# Loop Rows
for rownum in range(4, sh.nrows, 1):
   # If las - END
   if sh.cell_value(rownum,0) == 'TOTAL':
      break
   else:
      # Fill 5 First ROWS - If Empty fill with Last One Valid
      if sh.cell_value(rownum,0) != '':
         ESTAB_ENSINO = unicode(sh.cell_value(rownum, 0))
      if sh.cell_value(rownum, 1) != '':
         UNI_ORGANICA = unicode(sh.cell_value(rownum, 1))
      if sh.cell_value(rownum,2) != '':
         NIVEL_FORMACAO = unicode(sh.cell_value(rownum, 2))
      if sh.cell_value(rownum,3) != '':
         CURSO_SUP = unicode(sh.cell_value(rownum, 3))
      if sh.cell_value(rownum,4) != '':
         AREA_EDU_FOR = unicode(sh.cell_value(rownum, 4))

      # COLUNAS
      listbdd[:] = []
      # Loop Cols
      for col in range(sh.ncols):
         if (col != checkCol):
            # If no value - Fill 0.0
            if sh.cell(rownum,col).value == "-":
               listbdd.append(unicode("0.0"))
            elif isinstance(sh.cell(rownum,col).value, float):
               # If is a Float
               listbdd.append(unicode((sh.cell(rownum,col).value)))
            else:
               # If First 5 Empty - Fill With Last Valid
               if (sh.cell(rownum,col).value) == '' and col == 0:
                 listbdd.append(ESTAB_ENSINO)
               elif (sh.cell(rownum,col).value) == '' and col == 1:
                 listbdd.append(UNI_ORGANICA)
               elif (sh.cell(rownum,col).value) == '' and col == 2:
                 listbdd.append(NIVEL_FORMACAO)
               elif (sh.cell(rownum,col).value) == '' and col == 3:
                 listbdd.append(CURSO_SUP)
               elif (sh.cell(rownum,col).value) == '' and col == 4:
                 listbdd.append(AREA_EDU_FOR)
               else:
                  listbdd.append(unicode(sh.cell(rownum,col).value)) 
      # Object Class BDD Values
      session.add(CLS_Table(
                           listbdd[0], 
                           listbdd[1], 
                           listbdd[2], 
                           listbdd[3], 
                           listbdd[4], 
                           listbdd[5], 
                           listbdd[6], 
                           listbdd[7], 
                           listbdd[8], 
                           listbdd[9], 
                           listbdd[10],                            
                           listbdd[11], 
                           listbdd[12], 
                           listbdd[13], 
                           listbdd[14], 
                           listbdd[15], 
                           listbdd[16], 
                           listbdd[17], 
                           listbdd[18], 
                           listbdd[19], 
                           listbdd[20],                            
                           listbdd[21], 
                           listbdd[22], 
                           listbdd[23], 
                           listbdd[24], 
                           listbdd[25], 
                           listbdd[26], 
                           listbdd[27], 
                           listbdd[28], 
                           listbdd[29],
                           listbdd[30],                            
                           listbdd[31], 
                           listbdd[32], 
                           listbdd[33], 
                           listbdd[34], 
                           listbdd[35], 
                           listbdd[36], 
                           listbdd[37], 
                           listbdd[38], 
                           listbdd[39], 
                           listbdd[40],                            
                           listbdd[41], 
                           listbdd[42], 
                           listbdd[43], 
                           listbdd[44], 
                           listbdd[45], 
                           listbdd[46], 
                           listbdd[47], 
                           listbdd[48], 
                           listbdd[49], 
                           listbdd[50], 
                           listbdd[51], 
                           listbdd[52]
                           ))
         # FIM COLUNAS

   # FIM LINHAS

# commit the record the database
session.commit()
print "\n### - END - ###\n"